/**
 * Copyright (C) 2008-2009 Cristian Olaru <colaru@gmail.com>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *         http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.integrationpath.mengine.dao.hibernate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.integrationpath.mengine.dao.UtilityDao;
import com.integrationpath.mengine.model.Article;
import com.integrationpath.mengine.model.Comment;
import com.integrationpath.mengine.model.Rating;

public class UtilityDaoHibernate extends HibernateDaoSupport implements UtilityDao {

	public boolean isRatedFirstTime(Long articleId, String ip) {
		List list = (List) getHibernateTemplate().find("from Rating r where r.article.id=? and r.ip=?", new Object[] {articleId, ip});
		return list.size() == 0;
	}

	public Double calculateRating(Long articleId) {
		double rating = 0;
		List list = (List) getHibernateTemplate().find("from Rating r where r.article.id=?", new Object[] {articleId});
		for (Object object : list) {
			Rating ratingObject = (Rating)object;
			rating += ratingObject.getNumber();
		}
		if(list.size()!=0)
			return rating/list.size();
		else
			return 0.00;
	}

	public List getAllTagsOnce() {
		return getHibernateTemplate().find("select t.name, count(t) from Tag t where t.article!=null group by t.name");
	}

	public List getAllCategoriesOnce() {
		return getHibernateTemplate().find("select a.category.name, count(a) from Article a group by a.category.name");
	}

	public Long getUsersNumber() {
		List list = getHibernateTemplate().find("select count(u) from AppUser u");
		return (Long)list.get(0);
	}

	public Integer getMediaNumberByType(Integer type, Long articleId) {
		List list = getHibernateTemplate().find("select count(m) from Media m where m.mediaType.id=? and m.article.id=?", new Object[] {type, articleId});
		Long number =  (Long)list.get(0);
		return number.intValue();
	}

	public boolean isEmailInDatabase(String email) {
		List list = getHibernateTemplate().find("select count(u) from AppUser u where u.email=?", new Object[] {email});
		return (Long)list.get(0)!=0;
	}

	public boolean isUseridInDatabase(String userid) {
		List list = getHibernateTemplate().find("select count(u) from AppUser u where u.username=?", new Object[] {userid});
		return (Long)list.get(0)!=0;
	}

	public List getLastComments(int size) {
		Criteria criteria = getSession().createCriteria(Comment.class);
		criteria.addOrder(Order.desc("date"));
		criteria.setMaxResults(size);
		criteria.setCacheable(true);
		criteria.add(Expression.eq("status",new Byte("1")));
		return criteria.list();
	}
	
	public List getAllComments() {
		Criteria criteria = getSession().createCriteria(Comment.class);		
		criteria.setCacheable(true);
		return criteria.list();
	}

	public ResultSet executeQuery(String query) throws SQLException {	
		PreparedStatement ps = null;
		ResultSet rs = null;
		Session session = getSession();
		Connection connection = session.connection();
		ps = connection.prepareStatement(query);		
		return ps.executeQuery();	
	}
}
